/*-------------------<-- Start of Description-->---------------------\ | Fast Retrieve Data from Device Database, very fast, more than 8 | | times faster than the regular query; | |---------------------<-- End of Description-->----------------------| |--------------------------------------------------------------------| |------------<-- Start of Files or Arguments Needed-->---------------| | libname: a defined database library name; | | dbtable: the table name in the database; | | NOTE: if libname is given as libname.dataname, then the macro will | | take the name before . as the library and the name after . as| | the dataset name; | | where: the where condition, if you want to select only a subset | | of all the entire table; | | dbkey: the key of the database table; | | orderby: sort the output data by; | | schema: the schema, such as the "acedaf$current" for table | | "acedaf$current.pat_dvn" in the OC; | | outdata: the output dataset created; | | NOTE: if the outdata is given as lib., then the macro will take | | dbtable name as the output dataset name, and save it to the | | library under lib; | | outlib: the output library; | | NOTE: if the outlib is given as lib.dataname, then the macro will | | use it as an output dataset; if outlib is given as lib or | | lib. and output as dataname, then the macro will use | | lib.dataname as the output dataset; | |-------------<-- End of Files or Arguments Needed-->----------------| |--------------------------------------------------------------------| |------------------<-- Start of Files Created-->---------------------| | Example: | | %macro sqlconcat(indata=, var=, outvar=); | | %global &outvar; | | %local localv1; | | proc sql noprint; | | select distinct &var | | into :localv1 separated by '", "' | | from &indata | | where &var ne ' ' | | order by &var.; | | quit; | | %let localv1="&localv1"; | | %let &outvar=(&localv1); | | %put &outvar is &&&outvar; | | %mend sqlconcat; | | %sqlconcat(indata=acedcrf.implant, var=serialno, | | outvar=acedallserial);| | libname DVdbms odbc dsn='device' user=dr password=dr | | access=readonly connection=GLOBALREAD; | | %query(libname=DVdbms, dbtable=episode STD_PARAM_PACING, | | where=(comp_sernum in &acedallserial), dbkey=comp_sernum, | | orderby=comp_sernum, outdata=episode STD_PARAM_PACING, | | outlib=); | | Example 2: %query(datafax.base,orderby=ptid,outlib=extract.pat); | | Usage: %query(libname=,dbtable=, where=, dbkey=,orderby=, outdata=,| | outlib=); | \-------------------<-- End of Files Created-->---------------------*/ %macro query/parmbuff; /*--------------------------------------------\ | Copy Right: Duo Zhou; | | Created: 10-11-2001 8:43pm; | | Purpose: Retrieve a table from a Database; | \--------------------------------------------*/ %local ndsns localvname _i_ _j_ libname dbtable where dbkey orderby outdata outlib; %let libname=; %let dbtable=; %let where=; %let dbkey=; %let schema=; %let orderby=; %let outdata=; %let outlib=; %let syspbuff=%sysfunc(translate(%quote(%substr(%quote(%trim(%quote(%left(%quote(&syspbuff))))), 2, %eval(%length(%trim(%quote(%left(%quote(&syspbuff)))))-2))), %str(%'), %str(%"))); %let _queryrx_=%sysfunc(rxparse($(1))); %let _xquerypos_=0; %let _xquerylen_=0; %let _xqueryoldstr_=; %let _xquerynewstr_=; %do %while( %sysfunc(rxmatch(&_queryrx_, %quote(&syspbuff))) ); %syscall rxsubstr(_queryrx_, syspbuff, _xquerypos_, _xquerylen_); %let _xqueryoldstr_=%quote(%substr(%quote(&syspbuff), &_xquerypos_, &_xquerylen_)); %let _xquerynewstr_=%quote(%sysfunc(translate(%quote(&_xqueryoldstr_), À, %quote(%(), Á, %quote(%)), ´, %quote(,), ®, %quote( )))); %let syspbuff=%sysfunc(tranwrd(%quote(&syspbuff), %quote(&_xqueryoldstr_), %quote(&_xquerynewstr_))); %let _xquerypos_=0; %let _xquerylen_=0; %let _xqueryoldstr_=; %let _xquerynewstr_=; %end; %let _querynewstr_=&syspbuff; %local _xqueryvarcnt_ _xqueryvar_; %let _xqueryvarcnt_=0; %do %while(%length(%qscan(%nrbquote(&_querynewstr_), %eval(&_xqueryvarcnt_+1), %nrbquote(,)))); %let _xqueryvarcnt_=%eval(&_xqueryvarcnt_+1); %let _xqueryvar_=%nrbquote(%qscan(%nrbquote(&_querynewstr_), &_xqueryvarcnt_, %nrbquote(,))); %let _xqueryvar_=%sysfunc(translate(%quote(&_xqueryvar_), '(', 'À', ')', 'Á', ',', '´', ' ', '®')); %let _xqueryx2_=%trim(%left(%qscan(%quote(&_xqueryvar_), 1, %str(=)))); %let _xqueryx3_=%substr(%quote(&_xqueryvar_), %eval(%index(%quote(&_xqueryvar_),%str(=))+1), %eval(%length(&_xqueryvar_)-%index(%quote(&_xqueryvar_),%str(=)))); %if (not %index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&_xqueryvar_))))), %str(=))) %then %do; %if (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&_xqueryx2_))))), %str(%()) eq 1) and (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(%sysfunc(reverse(&_xqueryx2_))))))), %str(%))) eq 1) %then %let _xqueryx3_=%substr(%quote(%trim(%quote(%left(%quote(&_xqueryx2_))))), 2, %eval(%length(%trim(%quote(%left(%quote(&_xqueryx2_)))))-2)); %if (%quote(&_xqueryvarcnt_) = %quote(1)) %then %let libname=&_xqueryx3_; %else %if (%quote(&_xqueryvarcnt_) = %quote(2)) %then %let dbtable=&_xqueryx3_; %else %if (%quote(&_xqueryvarcnt_) = %quote(3)) %then %let where=&_xqueryx3_; %else %if (%quote(&_xqueryvarcnt_) = %quote(4)) %then %let dbkey=&_xqueryx3_; %else %if (%quote(&_xqueryvarcnt_) = %quote(5)) %then %let schema=&_xqueryx3_; %else %if (%quote(&_xqueryvarcnt_) = %quote(6)) %then %let orderby=&_xqueryx3_; %else %if (%quote(&_xqueryvarcnt_) = %quote(7)) %then %let outdata=&_xqueryx3_; %end; %else %if (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&_xqueryx3_))))), %str(%()) eq 1) and (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(%sysfunc(reverse(&_xqueryx3_))))))), %str(%))) eq 1) and (%index(%nrbquote(upcase(%nrbquote(%sysfunc(compress(%nrbquote(&_xqueryx2_)))))), WHERE=) le 1) %then %let &_xqueryx2_=%substr(%quote(%trim(%quote(%left(%quote(&_xqueryx3_))))), 2, %eval(%length(%trim(%quote(%left(%quote(&_xqueryx3_)))))-2)); %else %let &_xqueryx2_=&_xqueryx3_; %end; %if (%index(%quote(&libname), %quote(.))) and (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(%sysfunc(reverse(&libname))))))), %quote(.)) ne 1) %then %do; %let dbdata=%qscan(%quote(&libname), 2, %quote(.)); %if (%quote(&dbtable) ne) and (%quote(&dbtable) ne %quote(&dbdata)) %then %put ERROR: Inconsistency between libname %trim(%left(%upcase(&libname))) and database table names %trim(%left(%upcase(&dbtable)))!; %else %let dbtable=&dbdata; %let libname=%qscan(%quote(&libname), 1, %quote(.)); %end; %else %if (%quote(&libname) eq) %then %put ERROR: A database libname is required to proceed!; %if (%quote(&dbtable) eq) %then %put ERROR: A database table is required to proceed!; %if (%quote(&outdata) eq) %then %let outdata=&dbtable; %else %if (%index(%quote(&outdata), %quote(.))) and (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(%sysfunc(reverse(&outdata))))))), %quote(.)) eq 1) %then %let outdata=%trim(%left(&outdata))%trim(%left(&dbtable)); %if (%quote(&outlib) ne) %then %do; %if (%quote(&outlib) ne) and (not %index(%quote(&outlib), %quote(.))) and (%quote(&outdata) ne) and (not %index(%quote(&outdata), %quote(.))) %then %let outdata=%trim(%left(&outlib)).%trim(%left(&outdata)); %else %if (%quote(&outlib) ne) and (%index(%quote(&outlib), %quote(.))) and (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(%sysfunc(reverse(&outlib))))))), %quote(.)) eq 1) and (%quote(&outdata) ne) and (not %index(%quote(&outdata), %quote(.))) %then %let outdata=%trim(%left(&outlib))%trim(%left(&outdata)); %else %if (%quote(&outlib) ne) and (%index(%quote(&outlib), %quote(.))) and (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(%sysfunc(reverse(&outlib))))))), %quote(.)) ne 1) %then %let outdata=&outlib; %end; %if (%quote(&outdata) eq) %then %put ERROR: An output dataset name is required to proceed!; /******************/ %let dblib=%sysfunc(dequote(&libname)); %if (%quote(&orderby) ne) %then %if (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&orderby))))), %str(%()) eq 1) and (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(%sysfunc(reverse(&orderby))))))), %str(%))) eq 1) %then %let orderby=%substr(%quote(%trim(%quote(%left(%quote(&orderby))))), 2, %eval(%length(%trim(%quote(%left(%quote(&orderby)))))-2)); %let ndsns=1; %let dbtab1=%qscan(&dbtable, &ndsns, %str( )); %let data1=%qscan(&outdata, &ndsns, %str( )); %do %while(%length(&&dbtab&ndsns) gt 0); %let ndsns=%eval(&ndsns+1); %let dbtab&ndsns=%qscan(&dbtable, &ndsns, %str( )); %let data&ndsns=%qscan(&outdata,&ndsns,%str( )); %if &&data&ndsns eq %then %let data&ndsns=&&dbtab&ndsns; %end; %let ndsns =%eval(&ndsns-1); %if (%quote(&schema) ne) %then %let schema="%trim(%nrbquote(%left(%nrbquote(%sysfunc(dequote(&schema))))))"; %do _i_=1 %to &ndsns; proc sql; Create table &&data&_i_ as Select * from &dblib..&&dbtab&_i_ %if (%quote(&dbkey) ne) or (%quote(&schema) ne) %then %do; (%if (%quote(&dbkey) > 0) %then %do; dbkey=&dbkey %end; %if (%quote(&schema) ne) %then %do; schema=&schema %end;) %end; %if (%length(&where) > 0) %then %do; where &where %end; %if (%length(&orderby)> 0) %then %do; %if (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&orderby))))), %str(%()) eq 1) and (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(%sysfunc(reverse(%BQUOTE(&orderby)))))))), %str(%))) eq 1) %then %let orderby=%substr(%quote(%trim(%quote(%left(%quote(&orderby))))), 2, %eval(%length(%trim(%quote(%left(%quote(&orderby)))))-2)); %if (not %index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&orderby))))), %str(,))) %then %let orderby=%nrbquote(%sysfunc(translate(%nrbquote(&orderby), %str(,), %str( )))); order by &orderby %end;; quit; %end; %mend query;